package com.th.dao;

import com.th.entity.Obj;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.SQLException;
import java.util.Collection;
import java.util.Date;
import java.util.List;

public class HistoryDaoImp implements HistoryDao{
    JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void insertHistory(Obj obj) {
        String sql = "insert into history (get_id,get_date,get_add,obj_type,get_name,get_detail,get_num,lost_num,get_states,lost_date,img)"+
                " values(?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,obj.getGet_id(),obj.getGet_date(),obj.getGet_add(),obj.getObj_type(),obj.getGet_name(),obj.getGet_detail(),obj.getGet_num(),obj.getLost_num(),obj.getGet_states(),obj.getLost_date(),obj.getImg());
    }

    @Override
    public List<Obj> selectHistoryByGetNum(String get_num) {
        String sql = "select * from history where get_num = \'"+get_num+"\'";
        return jdbcTemplate.query(sql, new RowMapper<Obj>() {
            @Override
            public Obj mapRow(java.sql.ResultSet resultSet, int i) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(resultSet.getInt("get_id"));
                obj.setGet_date(resultSet.getDate("get_date"));
                obj.setGet_add(resultSet.getString("get_add"));
                obj.setObj_type(resultSet.getString("obj_type"));
                obj.setGet_name(resultSet.getString("get_name"));
                obj.setGet_detail(resultSet.getString("get_detail"));
                obj.setGet_num(resultSet.getString("get_num"));
                obj.setLost_num(resultSet.getString("lost_num"));
                obj.setGet_states(resultSet.getInt("get_states"));
                obj.setLost_date(resultSet.getDate("lost_date").toString());
                obj.setImg(resultSet.getString("img"));
                return obj;
            }
        });
    }

    @Override
    public List<Obj> selectHistoryByLostNum(String lost_num) {
        String sql = "select * from history where lost_num = \'"+lost_num+"\'";
        return jdbcTemplate.query(sql, new RowMapper<Obj>() {
            @Override
            public Obj mapRow(java.sql.ResultSet resultSet, int i) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(resultSet.getInt("get_id"));
                obj.setGet_date(resultSet.getDate("get_date"));
                obj.setGet_add(resultSet.getString("get_add"));
                obj.setObj_type(resultSet.getString("obj_type"));
                obj.setGet_name(resultSet.getString("get_name"));
                obj.setGet_detail(resultSet.getString("get_detail"));
                obj.setGet_num(resultSet.getString("get_num"));
                obj.setLost_num(resultSet.getString("lost_num"));
                obj.setGet_states(resultSet.getInt("get_states"));
                obj.setLost_date(resultSet.getDate("lost_date").toString());
                obj.setImg(resultSet.getString("img"));
                return obj;
            }
        });
    }

    @Override
    public List<Obj> selectHistoryByDate(int pagenum, int pagesize, String start, String end) {
        String sql = "select  * from history where get_date between \'"+start+"\' and \'"+end+"\' limit "+pagesize+" OFFSET "+(pagenum-1)*pagesize;
        return jdbcTemplate.query(sql, new RowMapper<Obj>() {
            @Override
            public Obj mapRow(java.sql.ResultSet resultSet, int i) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(resultSet.getInt("get_id"));
                obj.setGet_date(resultSet.getDate("get_date"));
                obj.setGet_add(resultSet.getString("get_add"));
                obj.setObj_type(resultSet.getString("obj_type"));
                obj.setGet_name(resultSet.getString("get_name"));
                obj.setGet_detail(resultSet.getString("get_detail"));
                obj.setGet_num(resultSet.getString("get_num"));
                obj.setLost_num(resultSet.getString("lost_num"));
                obj.setGet_states(resultSet.getInt("get_states"));
                obj.setLost_date(resultSet.getDate("lost_date").toString());
                obj.setImg(resultSet.getString("img"));
                return obj;
            }
        });
    }

    @Override
        public List<Obj> selectHistoryByKey(int page, int pageSize, String key) {
        StringBuilder sb = new StringBuilder();
        sb.append("%");
        for(int i = 0;i<key.length();i++)
        {
            sb.append(key.charAt(i));
            sb.append("%");
        }
        key = sb.toString();
        String sql = "select * from history where get_nun Regexp \'"+key+"\' limit "+pageSize+" OFFSET "+(page-1)*pageSize;
        return jdbcTemplate.query(sql, new RowMapper<Obj>() {
            @Override
            public Obj mapRow(java.sql.ResultSet resultSet, int i) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(resultSet.getInt("get_id"));
                obj.setGet_date(resultSet.getDate("get_date"));
                obj.setGet_add(resultSet.getString("get_add"));
                obj.setObj_type(resultSet.getString("obj_type"));
                obj.setGet_name(resultSet.getString("get_name"));
                obj.setGet_detail(resultSet.getString("get_detail"));
                obj.setGet_num(resultSet.getString("get_num"));
                obj.setLost_num(resultSet.getString("lost_num"));
                obj.setGet_states(resultSet.getInt("get_states"));
                obj.setLost_date(resultSet.getDate("lost_date").toString());
                obj.setImg(resultSet.getString("img"));
                return obj;
            }
        });
    }

    @Override
    public List<Obj> selectHistorys(int page, int pageSize) {
        String sql = "select * from history limit "+pageSize+" OFFSET "+(page-1)*pageSize;
        return jdbcTemplate.query(sql, new RowMapper<Obj>() {
            @Override
            public Obj mapRow(java.sql.ResultSet resultSet, int i) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(resultSet.getInt("get_id"));
                obj.setGet_date(resultSet.getDate("get_date"));
                obj.setGet_add(resultSet.getString("get_add"));
                obj.setObj_type(resultSet.getString("obj_type"));
                obj.setGet_name(resultSet.getString("get_name"));
                obj.setGet_detail(resultSet.getString("get_detail"));
                obj.setGet_num(resultSet.getString("get_num"));
                obj.setLost_num(resultSet.getString("lost_num"));
                obj.setGet_states(resultSet.getInt("get_states"));
                obj.setLost_date(resultSet.getDate("lost_date").toString());
                obj.setImg(resultSet.getString("img"));
                return obj;
            }
        });
    }
}
